<?php

namespace common\components\office\grid;

use Yii;
use yii\base\InvalidConfigException;
use common\i18n\Formatter;
use common\helpers\ArrayHelper;
use common\helpers\FileHelper;
use yii\helpers\Inflector;
use common\components\office\grid\Column;
use common\components\office\grid\DataColumn;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Protection;

/**
 * GridExcel
 *
 * @author emhome<emhome@163.com>
 * @since 2.0
 */
class GridExcel extends \yii\base\Widget {

    /**
     * @var array
     */
    const FORMAT_XLS = 'Xls';
    const FORMAT_XLSX = 'Xlsx';
    const FORMAT_ODS = 'Ods';
    const FORMAT_HTML = 'Csv';
    const FORMAT_CSV = 'Html';
    const FORMAT_TCPDF = 'Tcpdf';
    const FORMAT_DOMPDF = 'Dompdf';
    const FORMAT_MPDF = 'Mpdf';

    /**
     * @var array
     */
    public $data = [];

    /**
     * @var \yii\data\ActiveDataProvider the data provider for the view. This property is required.
     */
    public $dataProvider;

    /**
     * @var array
     */
    public $captions = [];

    /**
     * @var string
     */
    public $title;

    /**
     * @var string
     */
    public $filename;

    /**
     * @var string
     */
    public $savePath;

    /**
     * @var boolean to set the file excel to download mode.
     */
    public $asAttachment = false;

    /**
     * @var boolean if true, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted.
     */
    public $preCalculationFormula = false;

    /**
     * @var boolean Because of a bug in the Office2003 compatibility pack, there can be some small issues when opening Xlsx spreadsheets (mostly related to formula calculation)
     */
    public $compatibilityOffice2003 = false;

    /**
     * @var array grid column configuration. Each array element represents the configuration
     * for one particular grid column. For example,
     *
     * ```php
     * [
     *     ['class' => SerialColumn::className()],
     *     [
     *         'class' => DataColumn::className(), // this line is optional
     *         'attribute' => 'name',
     *         'format' => 'text',
     *         'label' => 'Name',
     *     ],
     *     ['class' => CheckboxColumn::className()],
     * ]
     * ```
     *
     * If a column is of class [[DataColumn]], the "class" element can be omitted.
     *
     * As a shortcut format, a string may be used to specify the configuration of a data column
     * which only contains [[DataColumn::attribute|attribute]], [[DataColumn::format|format]],
     * and/or [[DataColumn::label|label]] options: `"attribute:format:label"`.
     * For example, the above "name" column can also be specified as: `"name:text:Name"`.
     * Both "format" and "label" are optional. They will take default values if absent.
     *
     * Using the shortcut format the configuration for columns in simple cases would look like this:
     *
     * ```php
     * [
     *     'id',
     *     'amount:currency:Total Amount',
     *     'created_at:datetime',
     * ]
     * ```
     *
     * When using a [[dataProvider]] with active records, you can also display values from related records,
     * e.g. the `name` attribute of the `author` relation:
     *
     * ```php
     * // shortcut syntax
     * 'author.name',
     * // full syntax
     * [
     *     'attribute' => 'author.name',
     *     // ...
     * ]
     * ```
     */
    public $columns = [];

    /**
     * @var array grid column configuration. Each array element represents the configuration
     */
    public $totalColumns = [];

    /**
     * @var string the default data column class if the class name is not explicitly specified when configuring a data column.
     * Defaults to 'common\components\office\grid\DataColumn'.
     */
    public $dataColumnClass;

    /**
     * @var array|Formatter the formatter used to format model attribute values into displayable texts.
     * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
     * instance. If this property is not set, the "formatter" application component will be used.
     */
    public $formatter;

    /**
     * @var array|Formatter the formatter used to format model attribute values into displayable texts.
     * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
     * instance. If this property is not set, the "formatter" application component will be used.
     */
    public $format = self::FORMAT_XLSX;

    /**
     * @var string
     */
    public $emptyText = '';

    /**
     * @var mixed
     */
    public $rowSeparatorValue;

    /**
     * @var string
     */
    public $showRowSeparatorLine = false;

    /**
     * @var bool
     */
    public $protection = false;

    /**
     * @var array
     * ```
     * setPassword
     * setInsertRows
     * setInsertColumns
     * setSheet
     * setSort
     * setDeleteRows
     * setDeleteColumns
     * setFormatCells
     * ```
     */
    public $protectionOptions = [
        'setInsertRows' => false,
        'setInsertColumns' => false,
        'setDeleteRows' => true,
        'setDeleteColumns' => true,
    ];

    /**
     * @var bool
     */
    public $security = false;

    /**
     * @var array
     * ```
     * setLockWindows
     * setLockStructure
     * setWorkbookPassword
     */
    public $securityOptions = [
        'setLockWindows' => true,
        'setLockStructure' => true,
        'setWorkbookPassword' => 'PhpSpreadsheet',
    ];

    /**
     * @var bool
     */
    public $lockHeaderCells = true;

    /**
     * @var Spreadsheet
     */
    public $excelSpreadSheet;

    /**
     * @var Spreadsheet
     */
    private $_spreadSheet;

    /**
     * @var integer
     */
    private $_rowIndex = 1;

    /**
     * @var integer
     */
    private $_headerRowIndex;

    /**
     * @var integer
     */
    private $_totalAmount = [];

    /**
     * Initializes the grid view.
     * This method will initialize required property values and instantiate [[columns]] objects.
     */
    public function init() {
        parent::init();
        if ($this->dataProvider === null) {
            throw new InvalidConfigException('The "dataProvider" property must be set.');
        }
        $this->initFormatter();
        $this->createSpreadSheet();
        $this->initColumns();
        if (!empty($this->totalColumns)) {
            $this->_totalAmount = array_combine($this->totalColumns, array_fill(0, count($this->totalColumns), 0));
        }
    }

    /**
     * Runs the widget.
     */
    public function run() {
        $sheet = $this->renderSheet();
        if ($this->asAttachment) {
            return $this->renderAttachmentFile($sheet);
        }
        return $sheet;
    }

    /**
     * saving the xls file to download or to path
     */
    public function renderAttachmentFile($sheet) {
        if (!isset($this->format)) {
            $this->format = 'Xlsx';
        }
        $path = $this->getSavePath();
        /* @var $writer Xlsx */
        $writer = IOFactory::createWriter($sheet, $this->format);
        $writer->setOffice2003Compatibility($this->compatibilityOffice2003);
        $writer->setPreCalculateFormulas($this->preCalculationFormula);
        $writer->save($path);
        $response = Yii::$app->response;
        if ($path === 'php://output') {
            $handle = ob_get_contents();
            $response->sendContentAsFile($handle, $this->getFileName());
            ob_end_clean();
            Yii::$app->end();
        }
        $response->sendFile($path, $this->getFileName());
    }

    public function export() {
        $sheet = $this->renderSheet();
        if ($this->asAttachment) {
            return $this->renderAttachmentFile($sheet);
        }
        return $sheet;
    }

    /**
     * Formatter
     */
    public function initFormatter() {
        if ($this->formatter === null) {
            $this->formatter = Yii::$app->getFormatter();
        } elseif (is_array($this->formatter)) {
            $this->formatter = Yii::createObject($this->formatter);
        }
        if (!$this->formatter instanceof Formatter) {
            throw new InvalidConfigException('The "formatter" property must be either a Format object or a configuration array.');
        }
        $this->formatter->nullDisplay = '';
        $this->formatter->thousandSeparator = '';
    }

    /**
     * Spreadsheet
     */
    public function createSpreadSheet() {
        if ($this->excelSpreadSheet && $this->excelSpreadSheet instanceof Spreadsheet) {
            $this->_spreadSheet = $this->excelSpreadSheet;
        } else {
            $this->_spreadSheet = Yii::createObject(Spreadsheet::class);
        }
    }

    /**
     * @return Spreadsheet
     */
    public function getSpreadSheet() {
        return $this->_spreadSheet;
    }

    /**
     * 实例化 Spreadsheet sheet 对象
     * @return Worksheet
     */
    public function getActiveSheet() {
        $spreadsheet = $this->getSpreadSheet();
        return $spreadsheet->getActiveSheet();
    }

    /**
     * @return Spreadsheet
     */
    public function renderSheet() {
        $this->renderTitle();
        $this->renderTableCaption();

        $this->renderTableHeader();
        $this->renderTableBody();
        $this->renderTableFooter();
        $this->executeSheetProtection();
        return $this->getSpreadSheet();
    }

    /**
     * Set ActiveSheet Protection
     */
    public function executeSheetProtection() {
        if ($this->protection) {
            $protectionOptions = $this->protectionOptions;
            $sheetProtection = $this->getActiveSheet()->getProtection();
            foreach ($protectionOptions as $name => $value) {
                $method = Inflector::variablize('set_' . $name);
                $handler = array($sheetProtection, $method);
                if (method_exists($sheetProtection, $method) && is_callable($handler)) {
                    call_user_func_array($handler, [$value]);
                }
            }
            $sheetProtection->setSheet(true);
        }
        if ($this->security) {
            $securityOptions = $this->securityOptions;
            $sheetSecurity = $this->getSpreadSheet()->getSecurity();
            foreach ($securityOptions as $name => $value) {
                $method = Inflector::variablize('set_' . $name);
                $handler = array($sheetSecurity, $method);
                if (method_exists($sheetSecurity, $method) && is_callable($handler)) {
                    call_user_func_array($handler, [$value]);
                }
            }
        }
    }

    /**
     * Creates column objects and initializes them.
     */
    protected function initColumns() {
        if (empty($this->columns)) {
            $this->guessColumns();
        }
        foreach ($this->columns as $i => $column) {
            if (is_string($column)) {
                $column = $this->createDataColumn($column);
            } else {
                $column = Yii::createObject(array_merge([
                    'class' => $this->dataColumnClass ?: DataColumn::className(),
                    'grid' => $this,
                ], $column));
            }
            if (!$column->visible) {
                unset($this->columns[$i]);
                continue;
            }
            $this->columns[$i] = $column;
        }
    }

    /**
     * Get the number of data columns.
     * @return integer
     */
    public function getColspan() {
        return count($this->columns);
    }

    /**
     * Renders the title of the ActiveSheet.
     */
    public function renderTitle() {
        if ($this->title === false || $this->title === null) {
            return;
        }
        $titleOptions = $this->getTitleOptions();
        $sheet = $this->getActiveSheet();
        $rowIndex = $this->_rowIndex;
        $row = $sheet->getRowDimension($rowIndex);
        $height = ArrayHelper::remove($titleOptions, 'height');
        if ($height) {
            $row->setRowHeight($height);
        }
        $cell = $sheet->getCellByColumnAndRow(1, $rowIndex);
        $cell->setValue($this->title);
        $params = [
            1,
            $rowIndex,
            $this->colspan,
            $rowIndex
        ];
        $method = array($sheet, 'mergeCellsByColumnAndRow');
        call_user_func_array($method, $params);
        if (!empty($titleOptions)) {
            $style = call_user_func_array(array($sheet, 'getStyleByColumnAndRow'), $params);
            $style->applyFromArray($this->getTitleOptions());
        }
        $this->_rowIndex++;
    }

    /**
     * 获取标题设置
     * @return array
     */
    public function getTitleOptions() {
        return [
            'font' => [
                'name' => 'Arial',
                'bold' => true,
                'italic' => false,
                'underline' => Font::UNDERLINE_DOUBLE,
                'strikethrough' => false,
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'borders' => [
                'bottom' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ],
                'top' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ]
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'quotePrefix' => true,
            'height' => 30
        ];
    }

    /**
     * Renders the caption of the ActiveSheet table.
     */
    public function renderTableCaption() {
        if (empty($this->captions)) {
            return;
        }
        $sheet = $this->getActiveSheet();
        foreach ($this->captions as $item) {
            $styles = ArrayHelper::getValue($item, 'styles', []);
            $height = ArrayHelper::getValue($item, 'height');
            $items = ArrayHelper::getValue($item, 'items', []);
            $rowIndex = $this->_rowIndex;
            $row = $sheet->getRowDimension($rowIndex);
            if ($height) {
                $row->setRowHeight($height);
            }
            $cells = (array) $items;
            foreach ($cells as $colIndex => $column) {
                $cell = $sheet->getCellByColumnAndRow($colIndex + 1, $rowIndex);
                $text = '';
                $_cols = 1;
                $_rows = 1;
                $_styles = [];
                if (is_string($column)) {
                    $text = $column;
                } elseif (is_array($column)) {
                    $text = ArrayHelper::getValue($column, 'text');
                    $_cols = ArrayHelper::getValue($column, 'cols', 1);
                    $_rows = ArrayHelper::getValue($column, 'rows', 1);
                    $_styles = ArrayHelper::getValue($column, 'styles', []);
                }
                if ($text) {
                    $cell->setValue($text);
                }
                $_cellStyles = ArrayHelper::merge($styles, $_styles);
                $params = [
                    $colIndex + 1,
                    $rowIndex,
                    $colIndex + $_cols,
                    $rowIndex + $_rows
                ];
                $method = array($sheet, 'mergeCellsByColumnAndRow');
                call_user_func_array($method, $params);
                if (is_array($_cellStyles) && !empty($_cellStyles)) {
                    $cellStyle = call_user_func_array(array($sheet, 'getStyleByColumnAndRow'), $params);
                    $cellStyle->applyFromArray($_cellStyles);
                }
            }
            $this->_rowIndex++;
        }
    }

    /**
     * Renders the header of the ActiveSheet table.
     */
    public function renderTableHeader() {
        $sheet = $this->getActiveSheet();
        $rowHeight = 50;
        $rowIndex = $this->_rowIndex;
        $this->_headerRowIndex = $rowIndex;
        $row = $sheet->getRowDimension($rowIndex);
        if ($rowHeight) {
            $row->setRowHeight($rowHeight);
        }
        $colIndex = 1;
        $locked = $this->lockHeaderCells;
        foreach ($this->columns as $column) {
            /* @var $column DataColumn */
            $column->setColumnCellDataValidation($this->getSpreadSheet());
            $text = $column->renderHeaderCell();
            $cell = $sheet->getCellByColumnAndRow($colIndex, $rowIndex);
            if ($text) {
                $cell->setValue($text);
            }
            $cellStyle = $sheet->getStyleByColumnAndRow($colIndex, $rowIndex);
            $headerOptions = $column->headerOptions;
            if (is_array($headerOptions) && !empty($headerOptions)) {
                $width = ArrayHelper::remove($headerOptions, 'width');
                if ($width) {
                    $sheet->getColumnDimensionByColumn($colIndex)->setWidth($width);
                } else {
                    $sheet->getColumnDimensionByColumn($colIndex)->setAutoSize(true);
                }
                $cellStyle->applyFromArray($headerOptions);
            }
            if ($this->protection && is_bool($locked)) {
                $cellStyle->getProtection()->setLocked($locked ? Protection::PROTECTION_PROTECTED : Protection::PROTECTION_UNPROTECTED);
            }
            $colIndex++;
        }
        $this->_rowIndex++;
    }

    /**
     * Renders the body of the ActiveSheet table.
     */
    public function renderTableBody() {
        $total = $this->dataProvider->getTotalCount();
        if (!$total) {
            return $this->renderEmpty();
        }
        $models = array_values($this->dataProvider->getModels());
        $keys = $this->dataProvider->getKeys();
        $startRowIndex = $this->_rowIndex;
        foreach ($models as $index => $model) {
            $key = $keys[$index];
            $this->renderTableRow($model, $key, $index);
            $this->_rowIndex++;
        }
        $params = [
            1,
            $startRowIndex - 1,
            $this->colspan,
            $this->_rowIndex - 1
        ];
        $rowCellStyle = call_user_func_array(array($this->getActiveSheet(), 'getStyleByColumnAndRow'), $params);
        $rowCellStyle->applyFromArray([
            'borders' => [
                'top' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
                'left' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
                'bottom' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
                'right' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
            ],
        ]);
    }

    /**
     * Renders a table row with the given data model and key.
     * @param mixed $model the data model to be rendered
     * @param mixed $key the key associated with the data model
     * @param int $index the zero-based index of the data model among the model array returned by [[dataProvider]].
     * @return string the rendering result
     */
    public function renderTableRow($model, $key, $index) {
        $sheet = $this->getActiveSheet();
        $rowIndex = $this->_rowIndex;
        $colIndex = 1;
        $useTotal = !empty($this->totalColumns);
        /* @var $column DataColumn */
        foreach ($this->columns as $column) {
            $text = $column->renderDataCell($model, $key, $index);
            if ($column->borderSeparation) {
                if ($this->rowSeparatorValue !== $text) {
                    $this->showRowSeparatorLine = true;
                } else {
                    $this->showRowSeparatorLine = false;
                }
                $this->rowSeparatorValue = $text;
            }
            if ($useTotal && $column instanceof DataColumn) {
                $_temp = ArrayHelper::getValue($this->_totalAmount, $column->attribute, false);
                if ($_temp !== false) {
                    ArrayHelper::setValue($this->_totalAmount, $column->attribute, (float) $text + $_temp);
                }
            }
            $column->renderSheetCell($text, $sheet, $colIndex, $rowIndex);
            $colIndex++;
        }
        if ($this->showRowSeparatorLine) {
            $params = [
                1,
                $rowIndex,
                $colIndex - 1,
                $rowIndex
            ];
            $rowCellStyle = call_user_func_array(array($sheet, 'getStyleByColumnAndRow'), $params);
            $rowCellStyle->applyFromArray([
                'borders' => [
                    'top' => [
                        'borderStyle' => Border::BORDER_THIN,
                    ],
                ],
            ]);
        }
    }

    /**
     * Renders the footer of the ActiveSheet table.
     */
    public function renderTableFooter() {
        if (empty($this->totalColumns)) {
            return;
        }
        $sheet = $this->getActiveSheet();
        $colIndex = 1;
        /* @var $column DataColumn */
        foreach ($this->columns as $column) {
            if ($column instanceof DataColumn) {
                $text = ArrayHelper::getValue($this->_totalAmount, $column->attribute);
            } else {
                $text = $column->footer;
            }
            $cell = $sheet->getCellByColumnAndRow($colIndex++, $this->_rowIndex);
            $cell->setValue($text);
        }
        $this->_rowIndex++;
    }

    /**
     * Renders the HTML content indicating that the list view has no data.
     * @return string the rendering result
     * @see emptyText
     */
    public function renderEmpty() {
        if ($this->emptyText === false) {
            return '';
        }
        return $this->emptyText;
    }

    /**
     * Renders the footer of the ActiveSheet table.
     * @return string outfile path
     */
    public function getSavePath() {
        $path = 'php://output';
        if ($this->savePath && $this->savePath !== $path) {
            $path = Yii::getAlias($this->savePath);
            FileHelper::createDirectory($path);
            return $path . DIRECTORY_SEPARATOR . $this->generateFileTempName();
        }
        return $path;
    }

    /**
     * Generate temporary file name.
     */
    public function generateFileTempName() {
        $extension = mb_strtolower($this->format);
        return FileHelper::buildUploadFileName($extension);
    }

    /**
     * Generate temporary file name.
     */
    public function getFileName() {
        $extension = mb_strtolower($this->format);
        $filename = $this->filename ?: $this->title;
        $ext = pathinfo($filename, PATHINFO_EXTENSION);
        if (!$ext) {
            $filename .= '.' . $extension;
        } else {
            $filename = str_replace('.' . $ext, '.' . $extension, basename($filename));
        }
        return $filename;
    }

    /**
     * Creates a [[DataColumn]] object based on a string in the format of "attribute:format:label".
     * @param string $text the column specification string
     * @return DataColumn the column instance
     * @throws InvalidConfigException if the column specification is invalid
     */
    protected function createDataColumn($text) {
        if (!preg_match('/^([^:]+)(:(\w*))?(:(.*))?$/', $text, $matches)) {
            throw new InvalidConfigException('The column must be specified in the format of "attribute", "attribute:format" or "attribute:format:label"');
        }
        return Yii::createObject([
            'class' => $this->dataColumnClass ?: DataColumn::className(),
            'grid' => $this,
            'attribute' => $matches[1],
            'format' => isset($matches[3]) ? $matches[3] : 'text',
            'label' => isset($matches[5]) ? $matches[5] : null,
        ]);
    }

    /**
     * This function tries to guess the columns to show from the given data
     * if [[columns]] are not explicitly specified.
     */
    protected function guessColumns() {
        $models = $this->dataProvider->getModels();
        $model = reset($models);
        if (is_array($model) || is_object($model)) {
            foreach ($model as $name => $value) {
                if ($value === null || is_scalar($value) || is_callable([$value, '__toString'])) {
                    $this->columns[] = (string) $name;
                }
            }
        }
    }

}
